﻿using HeSeHus.Import.Interface;
//using HeSeHus.Model;
using HeSeHus.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Windows.Media;



namespace HeSeHus.Controller
{
    public class DatabaseController
    {
        private SqlConnection _sqlCon;
        private SqlCommand _sqlComm;
        private string _prod_DB_ConnectionString;
        private string _native_DB_ConnectionString;

        #region Properties
        public string Prod_DB_ConnectionString
        {
            get { return _prod_DB_ConnectionString; }
            set { _prod_DB_ConnectionString = value; }
        }



        public string Native_DB_ConnectionString
        {
            get { return _native_DB_ConnectionString; }
            set { _native_DB_ConnectionString = value; }
        }
        #endregion




        /// <summary>
        /// @Rasmus
        /// </summary>
        /// <param name="controllerFlag">Flag til at kontrollerer om det er Productionsdata eller NATIVE data som skal behandles
        /// <para>False: Production DB</para>
        /// <para>True: Native DB</para>
        /// </param>
        public DatabaseController(bool controllerFlag, string connectionString)
        {
            _sqlCon = new SqlConnection();
            _sqlComm = new SqlCommand();

            if (controllerFlag)
            {
                _native_DB_ConnectionString = connectionString;
                _sqlCon.ConnectionString = _native_DB_ConnectionString;
                _sqlComm.Connection = _sqlCon;

            }
            else
            {
                _prod_DB_ConnectionString = connectionString;
                _sqlCon.ConnectionString = _prod_DB_ConnectionString;
                _sqlComm.Connection = _sqlCon;
            }
        }
        public DatabaseController()
        {
                
        }



        public void PopulateProducts(Dictionary<IProduct, List<IProductDetail>> objectDic)
        {
            _sqlComm.CommandType = CommandType.StoredProcedure;
            _sqlComm.CommandText = "SP_Initial_Populate_Products";

            SqlParameter param_isAvailable;
            SqlParameter param_isNative;
            SqlParameter param_isDeleted;
            SqlParameter param_ProductId;
            SqlParameter param_ProductStatus;
            SqlParameter param_Name;

            foreach (var product in objectDic)
            {
                _sqlComm.Parameters.Clear();

                param_isAvailable = new SqlParameter("@products_isAvailable", SqlDbType.Char);
                param_isAvailable.Value = product.Key.AvailabilityLevel;
                _sqlComm.Parameters.Add(param_isAvailable);

                param_isNative = new SqlParameter("@products_isNative", SqlDbType.Bit);
                param_isNative.Value = 1;
                _sqlComm.Parameters.Add(param_isNative);

                param_isDeleted = new SqlParameter("@products_isDeleted", SqlDbType.Bit);
                param_isDeleted.Value = 0;
                _sqlComm.Parameters.Add(param_isDeleted);

                param_ProductId = new SqlParameter("@products_ProductId", SqlDbType.Int);
                param_ProductId.Value = int.Parse(product.Key.ProductNumber);
                _sqlComm.Parameters.Add(param_ProductId);

                param_ProductStatus = new SqlParameter("@products_ProductStatus", SqlDbType.NVarChar);
                param_ProductStatus.Value = product.Key.ProductStatus;
                _sqlComm.Parameters.Add(param_ProductStatus);

                param_Name = new SqlParameter("@products_Name", SqlDbType.NVarChar);
                param_Name.Value = "";
                _sqlComm.Parameters.Add(param_Name);

                try
                {
                    _sqlCon.Open();
                    _sqlComm.ExecuteNonQuery();
                }
                catch (SqlException)
                {

                    throw;
                }
                finally
                {
                    if (_sqlCon.State == ConnectionState.Open)
                    {
                        _sqlCon.Close();
                    }
                }

            }
        }

        public void PopulateProperties(Dictionary<IMProduct, List<IMProperty>> propertyDic)
        {
            _sqlComm.CommandType = CommandType.StoredProcedure;
            _sqlComm.CommandText = "SP_Initial_Populate_Properties";
            SqlCommand internalSqlComm = new SqlCommand();

            SqlParameter param_PropertyId;
            SqlParameter param_PropertyName;

            foreach (var product in propertyDic)
            {

                for (int i = 0; i < propertyDic[product.Key].Count; i++)
                {
                    _sqlComm.Parameters.Clear();

                    param_PropertyId = new SqlParameter("@PropertyId", SqlDbType.Int);
                    param_PropertyId.Value = propertyDic[product.Key][i].PropertiesId;
                    _sqlComm.Parameters.Add(param_PropertyId);

                    param_PropertyName = new SqlParameter("@PropertyName", SqlDbType.NVarChar);
                    param_PropertyName.Value = propertyDic[product.Key][i].Name;
                    _sqlComm.Parameters.Add(param_PropertyName);


                    internalSqlComm.Parameters.Clear();
                    internalSqlComm.CommandType = CommandType.StoredProcedure;
                    internalSqlComm.CommandText = "SP_Check_Property_Exists";


                    internalSqlComm.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int));
                    internalSqlComm.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;

                    param_PropertyId = new SqlParameter("@PropertyId", SqlDbType.Int);
                    param_PropertyId.Value = propertyDic[product.Key][i].PropertiesId;
                    internalSqlComm.Parameters.Add(param_PropertyId);

                    param_PropertyName = new SqlParameter("@PropertyName", SqlDbType.NVarChar);
                    param_PropertyName.Value = propertyDic[product.Key][i].Name;
                    internalSqlComm.Parameters.Add(param_PropertyName);

                    internalSqlComm.Connection = _sqlCon;

                    _sqlCon.Open();
                    internalSqlComm.ExecuteNonQuery();
                    _sqlCon.Close();
                    int valueReturned = (int)internalSqlComm.Parameters["@ReturnValue"].Value;

                    if (valueReturned == 0)
                    {
                        try
                        {
                            _sqlCon.Open();
                            _sqlComm.ExecuteNonQuery();
                        }
                        catch (SqlException)
                        {

                            throw;
                        }

                        finally
                        {
                            if (_sqlCon.State == ConnectionState.Open)
                            {
                                _sqlCon.Close();
                            }
                        }
                    }
                }
            }

        }

        public void PopulateProperties_Products(Dictionary<IMProduct, List<IMProperty>> propertyDic)
        {
            _sqlComm.CommandType = CommandType.StoredProcedure;
            _sqlComm.CommandText = "SP_Initial_Populate_usg_Properties_Product";

            
            SqlParameter param_ProductId;
            SqlParameter param_PropertyName;
            SqlParameter param_PropertyValue;

            foreach (var product in propertyDic)
            {

                for (int i = 0; i < propertyDic[product.Key].Count; i++)
                {
                    _sqlComm.Parameters.Clear();

                    param_PropertyName = new SqlParameter("@PropertyName", SqlDbType.NVarChar);
                    param_PropertyName.Value = propertyDic[product.Key][i].Name;
                    _sqlComm.Parameters.Add(param_PropertyName);

                    param_ProductId = new SqlParameter("@ProductId", SqlDbType.Int);
                    param_ProductId.Value = product.Key.ProdId;
                    _sqlComm.Parameters.Add(param_ProductId);

                    param_PropertyValue = new SqlParameter("@PropertyValue", SqlDbType.NVarChar);
                    param_PropertyValue.Value = propertyDic[product.Key][i].Value;
                    _sqlComm.Parameters.Add(param_PropertyValue);


                    try
                    {
                        _sqlCon.Open();
                        _sqlComm.ExecuteNonQuery();
                    }
                    catch (SqlException)
                    {

                        throw;
                    }

                    finally
                    {
                        if (_sqlCon.State == ConnectionState.Open)
                        {
                            _sqlCon.Close();
                        }
                    }

                }
            }
        }

        public void CleanDatabase()
        {
            _sqlComm.CommandText = "SP_Clean_Database";
            _sqlComm.CommandType = CommandType.StoredProcedure;

            try
            {
                _sqlCon.Open();
                _sqlComm.ExecuteNonQuery();
            }
            catch (SqlException)
            {

                throw;
            }
            finally
            {
                if (_sqlCon.State == ConnectionState.Open)
                {
                    _sqlCon.Close();
                }
            }
        }

        #region Ping metoder
        public ImageSource Ping(string connectionString, int port)
        {
            string uriPackUp = "pack://application:,,,/HeSeHus;component/Images/databaseUp.png";
            string uriPackDown = "pack://application:,,,/HeSeHus;component/Images/databaseDown.png";
            if (CheckServerAvailablity(connectionString, port))
            {
                return new ImageSourceConverter().ConvertFromString(uriPackUp) as ImageSource;

            }
            else
            {
                return new ImageSourceConverter().ConvertFromString(uriPackDown) as ImageSource;

            }
        }

        #pragma warning disable ///Blot for at fjerne warning fra error list
        public bool CheckServerAvailablity(string serverIPAddress, int port)
        {
            try
            {
                IPHostEntry ipHostEntry = Dns.Resolve(serverIPAddress);
                IPAddress ipAddress = ipHostEntry.AddressList[0];

                TcpClient TcpClient = new TcpClient();
                TcpClient.Connect(ipAddress, port);
                TcpClient.Close();

                return true;
            }
            catch
            {
                return false;
            }
        }
        
        #endregion    
    }


}
    

